package org.stvd.controller.codetable;

import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.stvd.common.Consts;
import org.stvd.common.aspectj.lang.annotation.Log;
import org.stvd.common.aspectj.lang.enums.BusinessType;
import org.stvd.common.utils.InterfaceResult;
import org.stvd.controller.BaseController;
import org.stvd.core.dto.QueryResult;
import org.stvd.core.util.convert.PyCode;
import org.stvd.entities.common.CodeTable;
import org.stvd.service.common.CodeTableService;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * @Title: CodeTableController
 * @Description: 公共码表-控制类 
 * @author houzx
 * @date 2020年2月8日
 */
@Controller
@RequestMapping("/codetable")
public class CodeTableController extends BaseController {

    @Autowired
    private CodeTableService codeTableService;

    private String tmpURL = "/codetable/";
    
    /**
     * @Description: 公共码表预览
     * @return
     */
    @RequestMapping(value = "preview")
    public String onCodeTablePreview(){
        String tableName = request.getParameter("tableName");
        tableName = (tableName==null) ? "" : tableName;
        List<Map<String, Object>> codeTableList = codeTableService.listAllCodeTable(tableName);
        map.put("codeTableList", codeTableList);
        map.put("tableName", tableName);
        return tmpURL + "CodeTablePreview.html";
    }
    
    /**
     * @Description: 新增码表消息
     * @param codeTable
     * @return
     */
    @GetMapping(value="addtype")
    public String onCodeTableTypeAddLoad(@ModelAttribute("codeTable") CodeTable codeTable){
        codeTable.setId("dic_");
        map.put("codeTable", codeTable);
        return tmpURL + "CodeTableTypeAdd.html";
    }
    
    @Log(title = "新增码表类别", businessType = BusinessType.INSERT)
    @PostMapping(value="addtype")
    @ResponseBody
    public InterfaceResult<Map<String, Object>> onCodeTableTypeAddAction(
        @Valid @ModelAttribute("codeTable") CodeTable codeTable, BindingResult bindingResult) {
        InterfaceResult<Map<String, Object>> result = new InterfaceResult<Map<String,Object>>();
        if (bindingResult.hasErrors()) {
            result.setError(bindingResult.getAllErrors().get(0).getDefaultMessage());
            return result;
        }
        result.transferResult(codeTableService.insertCodeTableType(codeTable));
        map.clear();
        return result;
    }
    
    /**
     * @Description: 修改码表信息
     * @param typeId 码表ID
     * @param codeTable
     * @return
     */
    @GetMapping(value="edittype/{typeId}")
    public String onCodeTableTypeEditLoad(@PathVariable String typeId, 
        @ModelAttribute("codeTable") CodeTable codeTable){
        Map<String, Object> tableMap = codeTableService.getCodeTableByName(typeId);
        if(tableMap!=null) {
            codeTable.setId((String)tableMap.get("table_name"));
            codeTable.setName((String)tableMap.get("table_comment"));
        }
        map.put("codeTable", codeTable);
        return tmpURL +"CodeTableTypeEdit.html";
    }
    
    @Log(title = "修改码表类别", businessType = BusinessType.UPDATE)
    @PostMapping(value="edittype")
    @ResponseBody
    public InterfaceResult<Map<String, Object>> onCodeTableTypeEditAction(
        @Valid @ModelAttribute("codeTable") CodeTable codeTable, BindingResult bindingResult){
        InterfaceResult<Map<String, Object>> result = new InterfaceResult<Map<String,Object>>();
        if (bindingResult.hasErrors()) {
            result.setError(bindingResult.getAllErrors().get(0).getDefaultMessage());
            return result;
        }
        result.transferResult(codeTableService.updateCodeTableType(codeTable));
        map.clear();
        return result;
    }
        
    /**
     * @Description: 删除码表信息
     * @param id 类码表ID
     * @param name 码表名称
     * @return
     */
    @Log(title = "删除码表类别", businessType = BusinessType.DELETE)
    @PostMapping(value="deltype")
    @ResponseBody
    public InterfaceResult<Map<String, Object>> onDicTypeDelete(String id, String name) {
        InterfaceResult<Map<String, Object>> result = new InterfaceResult<Map<String,Object>>();
        try{
            result.transferResult(codeTableService.deleteCodeTableType(id));
        }catch (Exception e) {
            e.printStackTrace();
            result.setError("删除"+name+"失败！<br/>失败原因："+e.getMessage());
        }
        return result ;
    }
    
    /**
     * @Description: 码表管理页面
     * @param tableName
     * @param codeTable
     * @param pageNo
     * @param map
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "{tableName}/management")
    public String onPageLoad(@PathVariable String tableName, @ModelAttribute("codeTable") CodeTable codeTable,
        @ModelAttribute("pageNo") Integer pageNo, ModelMap map) throws Exception {
        map.put("TableName", tableName);
        map.put("queryResult", onQuery(tableName, pageNo, Consts.PAGE_SIZE, codeTable.getId(), codeTable.getName()));
        return tmpURL + "CodeTableManagement.html";
    }

    @PostMapping(value = "{tableName}/query")
    @ResponseBody
    public QueryResult<CodeTable> onQuery(@PathVariable String tableName, int page, int rows, String id, String name)
        throws Exception {
        return codeTableService.getQueryResult(tableName, (page - 1) * rows + 1, rows, id, name, "");
    }

    /**
     * @Description: 新增码表数据
     * @param tableName
     * @param map
     * @return
     */
    @GetMapping(value = "{tableName}/add")
    public String onAddLoad(@PathVariable String tableName, ModelMap map) {
        map.put("tableName", tableName);
        map.put("codeTable", new CodeTable());
        return tmpURL + "CodeTableAdd.html";
    }

    @Log(title = "删除码表数据", businessType = BusinessType.INSERT)
    @PostMapping(value = "{tableName}/add")
    @ResponseBody
    public InterfaceResult<Map<String, Object>> onAddAction(@PathVariable String tableName, 
        @Valid @ModelAttribute("codeTable") CodeTable codeTable, BindingResult bindingResult,
        HttpServletRequest request, ModelMap map){
        InterfaceResult<Map<String, Object>> result = new InterfaceResult<Map<String,Object>>();
        if (bindingResult.hasErrors()) {
            result.setError(bindingResult.getAllErrors().get(0).getDefaultMessage());
            return result;
        }
        try {
            codeTableService.insert(tableName, codeTable.getId(), codeTable.getName(), codeTable.getPym(), codeTable.getPxh());
            result.setMsg("新增成功！");
        } catch (Exception e) {
            e.printStackTrace();
            result.setError("新增失败！失败原因："+ e.getMessage());
        }

        map.clear();
        return result;
    }

    /**
     * @Description: 编辑码表数据
     * @param tableName
     * @param id
     * @param map
     */
    @GetMapping(value = "{tableName}/edit/{id}")
    public String onEditLoad(@PathVariable String tableName, @PathVariable String id, ModelMap map){
        try {
            map.put("tableName", tableName);
            map.put("codeTable", codeTableService.findById(tableName, id));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tmpURL + "CodeTableEdit.html";
    }

    @Log(title = "修改码表数据", businessType = BusinessType.UPDATE)
    @PostMapping(value = "{tableName}/edit")
    @ResponseBody
    public InterfaceResult<Map<String, Object>> onEditAction(@PathVariable String tableName, 
        @Valid @ModelAttribute("codeTable") CodeTable codeTable, BindingResult bindingResult,
        HttpServletRequest request, ModelMap map){
        InterfaceResult<Map<String, Object>> result = new InterfaceResult<Map<String,Object>>();
        if (bindingResult.hasErrors()) {
            result.setError(bindingResult.getAllErrors().get(0).getDefaultMessage());
            return result;
        }
        try {
            codeTableService.update(tableName, codeTable.getId(), codeTable.getName(), codeTable.getPym(),codeTable.getPxh());
            result.setMsg("修改成功！");
        } catch (Exception e) {
            e.printStackTrace();
            result.setError("修改失败！失败原因："+ e.getMessage());
        }

        map.clear();
        return result;
    }

    /**
     * @Description: 生成拼音码
     * @param tableName
     */
    @PostMapping(value = "{tableName}/pym")
    @ResponseBody
    public InterfaceResult<Map<String, Object>> onCreatePym(@PathVariable String tableName){
        InterfaceResult<Map<String, Object>> result = new InterfaceResult<Map<String,Object>>();
        try {
            List<CodeTable> list = codeTableService.findAll(tableName);
            for (CodeTable codeTable : list) {
                codeTable.setPym(PyCode.getPinYinHeadChar(codeTable.getName()).toUpperCase());
                codeTableService.update(tableName, codeTable.getId(), codeTable.getName(),
                    PyCode.getPinYinHeadChar(codeTable.getName()).toUpperCase(), codeTable.getPxh());
            }
            result.setMsg("生成成功！");
        } catch (Exception e) {
            e.printStackTrace();
            result.setError("生成失败！失败原因：" + e.getMessage());
        }
        return result;
    }

    /**
     * @Description: 获取码表集合
     * @param tableName
     * @return
     * @throws Exception
     */
    @PostMapping(value = "{tableName}/findall")
    @ResponseBody
    public List<CodeTable> onFindAll(@PathVariable String tableName) {
        List<CodeTable> list = new ArrayList<CodeTable>();
        try {
            list = codeTableService.findAll(tableName);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * @Description:  删除码表数据
     * @param tableName
     * @param id
     * @return
     */
    @Log(title = "删除码表数据", businessType = BusinessType.DELETE)
    @PostMapping(value = "{tableName}/del")
    @ResponseBody
    public InterfaceResult<Map<String, Object>> onDelete(@PathVariable String tableName, String id) {
        InterfaceResult<Map<String, Object>> result = new InterfaceResult<Map<String,Object>>();
        try {
            codeTableService.delete(tableName, id);
            result.setMsg("码表[" + tableName + "]——" + id + "删除成功！");
        } catch (Exception e) {
            e.printStackTrace();
            result.setError("码表[" + tableName + "]——" + id + "删除失败！<br/>失败原因：" + e.getMessage());
        }
        return result;
    }
    
    /**
     * @Description: 码表导出
     * @param tableName
     * @param request
     * @param response
     * @param map
     * @return
     * @throws SQLException
     * @throws IOException
     */
    @Log(title = "导出码表数据", businessType = BusinessType.EXPORT)
    @PostMapping(value = "{tableName}/export")
    public String onExport(@PathVariable String tableName, HttpServletRequest request, HttpServletResponse response,
        ModelMap map) throws SQLException, IOException {
        response.reset();
        // 设置附件格式
        response.setHeader("Content-disposition", "attachment; filename=" + tableName + ".xls");// 设定输出文件头
        response.setContentType("application/msexcel");
        // 导出
        OutputStream ops = null;
        try {
            ops = new BufferedOutputStream(response.getOutputStream());
            WritableWorkbook wbook = Workbook.createWorkbook(ops); // 建立excel文件
            WritableSheet wsheet = wbook.createSheet(tableName, 0); // sheet名称

            //设置excel标题
            WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16,
            WritableFont.BOLD, false,
            UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
            WritableCellFormat wcfFC = new WritableCellFormat(wfont);
            wcfFC.setBackground(Colour.AQUA);
            wsheet.addCell(new Label(1, 0, tableName, wcfFC));
            wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14,
            WritableFont.BOLD, false,
            UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
            wcfFC = new WritableCellFormat(wfont);

            wsheet.addCell(new Label(0, 0, "ID"));
            wsheet.addCell(new Label(1, 0, "名称"));
            wsheet.addCell(new Label(2, 0, "拼音码"));
            wsheet.addCell(new Label(3, 0, "排序号"));

            String id = request.getParameter("id");
            String name = request.getParameter("name");
            List<CodeTable> list = codeTableService.findAll(tableName, id, name, "");
            for (int i = 0; i < list.size(); i++) {
                wsheet.addCell(new Label(0, i + 1, list.get(i).getId()));
                wsheet.addCell(new Label(1, i + 1, list.get(i).getName()));
                wsheet.addCell(new Label(2, i + 1, list.get(i).getPym()));
                wsheet.addCell(new Label(3, i + 1, list.get(i).getPxh().toString()));
            }
            wbook.write(); // 写入文件
            wbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ops.flush();
            ops.close();
        }
        return null;
    }

    /*
     * 省
     */
    @PostMapping(value = "province/query")
    @ResponseBody
    public List<CodeTable> getProvince() throws Exception {
        List<CodeTable> listAll = codeTableService.findAll("dic_native_place");
        Pattern pattern = Pattern.compile("^[0-9]{2}0000$");
        List<CodeTable> listProv = new ArrayList<CodeTable>();
        for (CodeTable nativePlace : listAll) {
            if (pattern.matcher(nativePlace.getId()).matches()) {
                listProv.add(nativePlace);
            }
        }
        return listProv;
    }

    /*
     * 市
     */
    @PostMapping(value = "city/{provID}/query")
    @ResponseBody
    public List<CodeTable> getCity(@PathVariable String provID) throws Exception {
        List<CodeTable> listAll = codeTableService.findAll("dic_native_place");
        List<CodeTable> listCity = new ArrayList<CodeTable>();
        if (provID.length() == 2) {
            Pattern pattern = Pattern.compile("^(?!("+provID+"0000))" + provID + "[0-9][0-9]00$");
            for (CodeTable nativePlace : listAll) {
                if (pattern.matcher(nativePlace.getId()).matches()) {
                    listCity.add(nativePlace);
                }
            }
        }
        return listCity;
    }

    /*
     * 县区
     */
    @PostMapping(value = "region/{cityID}/query")
    @ResponseBody
    public List<CodeTable> getRegion(@PathVariable String cityID) throws Exception {
        List<CodeTable> listAll = codeTableService.findAll("dic_native_place");
        List<CodeTable> listRegion = new ArrayList<CodeTable>();
        if (cityID.length() == 4) {
            Pattern pattern = Pattern.compile("^(?!("+cityID+"00))" + cityID + "[0-9][0-9]$");
            for (CodeTable nativePlace : listAll) {
                if (pattern.matcher(nativePlace.getId()).matches()) {
                    listRegion.add(nativePlace);
                }
            }
        }
        return listRegion;
    }

    /*
     * 仅适用三级目录，例如 010000，010100，010101
     */
    @PostMapping(value = "{tablename}/{level}/{uppercode}/query")
    @ResponseBody
    public List<CodeTable> getLevelFor3(@PathVariable String tablename, @PathVariable Integer level,
            @PathVariable String uppercode) throws Exception {
        List<CodeTable> listAll = codeTableService.findAll(tablename);
        List<CodeTable> listResult = new ArrayList<CodeTable>();
        String regex = "";
        switch (level) {
        case 1:
            regex = "^[0-9]{2}0000$";
            break;
        case 2:
            regex = "^(?!("+uppercode+"0000))" + uppercode + "[0-9][0-9]00$";
            break;
        case 3:
            regex = "^(?!("+uppercode+"00))" + uppercode + "[0-9][0-9]$";
            break;
        default:
            break;
        }
        Pattern pattern = Pattern.compile(regex);
        for (CodeTable codeTable : listAll) {
            if (pattern.matcher(codeTable.getId()).matches()) {
                listResult.add(codeTable);
            }
        }
        return listResult;
    }

    /*
     * 通过码表名称、级别、每级占位数、上级编码获取码表信息 例如：table_name/1/3/006/query, 匹配的id为：006,
     * level=1时，uppercode无效 例如：table_name/1/3/006/query, 匹配的id为：006001
     * 例如：table_name/3/3/006/query, 匹配的id为：006001001
     */
    @PostMapping(value = "{tablename}/{level}/{number}/{uppercode}/query")
    @ResponseBody
    public List<CodeTable> getLevel(@PathVariable String tablename, @PathVariable Integer level,
            @PathVariable Integer number, @PathVariable String uppercode) throws Exception {
        List<CodeTable> listAll = codeTableService.findAll(tablename);
        List<CodeTable> listResult = new ArrayList<CodeTable>();
        String regex = "";
        if (level.equals(1)) {
            regex = "[0-9]{" + number + "}";
        } else {
            regex = uppercode + "[0-9]{" + (level * number - uppercode.length()) + "}";
        }
        Pattern pattern = Pattern.compile(regex);
        for (CodeTable codeTable : listAll) {
            if (pattern.matcher(codeTable.getId()).matches()) {
                listResult.add(codeTable);
            }
        }
        return listResult;
    }
}
